#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.ol_edu_dm.dm_intent
with tmp as (select
        '2023-09-22' as date_time,
        case when grouping(b.day)=0 then 'day'
                when grouping(b.month)=0 then 'month'
                when grouping(b.year)=0 then 'year'
                else 'other' end as time_type,
       b.day,
       b.month,
       b.year,

       group_type as group_type_old,

       case when grouping(a.area)=0 then 'area'
             when grouping(a.itcast_subject_id)=0 then 'itcast_subject'
             when grouping(a.itcast_school_id)=0 then 'itcast_school'
             when grouping(a.origin_channel)=0 then 'origin_channel'
             when grouping(a.department_id)=0 then 'department'
             when grouping(a.origin_type)=0 then 'origin_type'
             when grouping(a.clue_state)=0 then 'clue_state'
             else 'all' end as group_type_new,

       a.origin_type,
       a.clue_state,
       a.area,
       a.itcast_subject_id,
       a.itcast_subject,
       a.itcast_school_id,
       a.itcast_school,
       a.origin_channel,
       a.department_id,
       a.department_name,

        sum(coalesce(a.intent_avl_count,0)) as intent_avl_count
from hive.ol_edu_dws.dws_intent_daycount a
left join hive.ol_edu_dwb.dwb_intent_detail  b on a.day=b.day
group by
grouping sets (
--日统计
        (b.day,a.origin_type,a.clue_state,a.group_type),
        (b.day,a.area,a.origin_type,a.clue_state,a.group_type),
        (b.day,a.itcast_subject_id,a.itcast_subject,a.origin_type,a.clue_state,a.group_type),
        (b.day,a.itcast_school_id,a.itcast_school,a.origin_type,a.clue_state,a.group_type),
        (b.day,a.origin_channel,a.origin_type,a.clue_state,a.group_type),
        (b.day,a.department_id,a.department_name,a.origin_type,a.clue_state,a.group_type),
--月统计
        (b.month,a.origin_type,a.clue_state,a.group_type),
        (b.month,a.area,a.origin_type,a.clue_state,a.group_type),
        (b.month,a.itcast_subject_id,a.itcast_subject,a.origin_type,a.clue_state,a.group_type),
        (b.month,a.itcast_school_id,a.itcast_school,a.origin_type,a.clue_state,a.group_type),
        (b.month,a.origin_channel,a.origin_type,a.clue_state,a.group_type),
        (b.month,a.department_id,a.department_name,a.origin_type,a.clue_state,a.group_type),
--年统计
        (b.year,a.origin_type,a.clue_state,a.group_type),
        (b.year,a.area,a.origin_type,a.clue_state,a.group_type),
        (b.year,a.itcast_subject_id,a.itcast_subject,a.origin_type,a.clue_state,a.group_type),
        (b.year,a.itcast_school_id,a.itcast_school,a.origin_type,a.clue_state,a.group_type),
        (b.year,a.origin_channel,a.origin_type,a.clue_state,a.group_type),
        (b.year,a.department_id,a.department_name,a.origin_type,a.clue_state,a.group_type)
))
select
        date_time,
        time_type,
        day,
        month,
        year,
        group_type_new,
        origin_type,
        clue_state,
        area,
        itcast_subject_id,
        itcast_subject,
        itcast_school_id,
        itcast_school,
        origin_channel,
        department_id,
        department_name,
        intent_avl_count
from tmp
where group_type_old=group_type_new;


insert into hive.ol_edu_dm.dm_clue
with tmp as (select
        '2023-09-22' as date_time,
        case  when grouping(b.day)=0 then 'day'
                else 'other' end as time_type,
       b.day,

       group_type as group_type_old,

       case when grouping(a.origin_type)=0 then 'origin_type'
             when grouping(a.clue_state)=0 then 'clue_state'
             else 'all' end as group_type_new,

        a.origin_type,
        a.clue_state,
        sum(coalesce(a.clue_avl_count,0)) as clue_avl_count,
        sum(coalesce(a.clue_total_count,0)) as clue_total_count
from hive.ol_edu_dws.dws_clue_hourcount a
left join hive.ol_edu_dwb.dwb_clue_detail  b on a.hour=b.hour
group by
grouping sets (
--日统计
        (b.day,a.origin_type,a.clue_state,a.group_type)
))
select
        date_time,
        time_type,
        day,
        group_type_new,
        origin_type,
        clue_state,
        clue_avl_count,
        clue_total_count
from tmp
where group_type_old=group_type_new;
"